<?php
/*
 * @Author: lzw
 * @Date: 2021-10-14 09:30:00
 * @LastEditTime: 2021-11-30 18:03:24
 * @LastEditors: 李明
 * @Description:
 * @FilePath: /CRM2.0-PHP/app/Http/Controllers/Management/DataImportController.php
 * @山东壹零贰肆信息科技有限公司
 */

namespace App\Http\Controllers\Management;

use App\Http\Controllers\common\CommonFun;
use App\Http\Controllers\common\LMReturn;
use PhpOffice\PhpSpreadsheet\Shared\Date;
use PhpOffice\PhpSpreadsheet\IOFactory;
use App\Http\Controllers\Controller;
use App\Models\WorkerBranchManage;
use Illuminate\Support\Facades\DB;
use App\Models\InterestRecord;
use App\Models\WorkerBranch;
use Illuminate\Http\Request;
use App\Models\WorkerRole;
use App\Models\BankCard;
use App\Models\Product;
use App\Models\Branch;
use App\Models\Worker;
use App\Models\Order;
use App\Models\User;
use App\Models\Bank;
use App\Models\OrderRecord;

class DataImportController extends Controller
{
    //数据录入
    public function DataImport(Request $request)
    {
        // 去验证token值
        $tokenInfo = json_decode($this->verifyTokenLm(), true);
        //判断传递过来的token的code码是否等于400  如果是400的话，提示token不正确
        if ($tokenInfo['code'] == 400) {
            return LMReturn::Error($tokenInfo['msg'], $tokenInfo['code']);
        }
        $operation_id = $tokenInfo['worker_id'];
        $all = $request->all();
        // 判断必要参数是否传入
        if (empty($all['long_time'])) {
            return LMReturn::insertErr('未传入投资期限', '403');
        }
        if (empty($all['pay_type'])) {
            return LMReturn::insertErr('未传入结算方式', '403');
        }
        if (empty($all['rate'])) {
            return LMReturn::insertErr('未传入年利率', '403');
        }
        // 查找产品信息
        $pay_type = $this->switchCase($all['pay_type']);
        if ($pay_type == 2) {
            $productList = Product::where('rate', ($all['rate'] * 100))->where('long_time', $all['long_time'])->where('pay_type', '4')->get();
        } else {
            $productList = Product::where('long_time', $all['long_time'])->where('rate', ($all['rate'] * 100))->where('pay_type', '2')->where('pay_long', $pay_type)->get();
        }
        if (count($productList) == 0) {
            return LMReturn::insertErr('未查到该产品，请录入后再添加信息', '403');
        } else {
            if (count($productList) > 1) {
                return LMReturn::insertErr('产品存在问题', '403');
            } else {
                //得到产品的id
                $product_id = $productList->first()->id;
            }
        }
        DB::beginTransaction();                                 // 开启数据库事务
        // 存入订单
        try {
            $branches = Branch::where('name', $all['branch'])->first();
            $branch_id = $branches->id;
            // 获得客户经理
            $worker_list = [];
            if (isset($all['customer_mangaer']) and $all['customer_mangaer'] != '') {
                $worker_dic = [];
                $worker_dic['name'] = $all['customer_mangaer'];
                $worker_dic['role_id'] = 44;
                $worker_list[] = $worker_dic;
            }
            if (isset($all['group_manager'])) {
                $worker_dic = [];
                $worker_dic['name'] = $all['group_manager'];
                $worker_dic['role_id'] = 33;
                $worker_list[] = $worker_dic;
            }
            if (isset($all['department_manager'])) {
                $worker_dic = [];
                $worker_dic['name'] = $all['department_manager'];
                $worker_dic['role_id'] = 32;
                $worker_list[] = $worker_dic;
            }
            if (isset($all['branch_manager'])) {
                $worker_dic = [];
                $worker_dic['name'] = $all['branch_manager'];
                $worker_dic['role_id'] = 31;
                $worker_list[] = $worker_dic;
            }
            if (isset($all['big_branch_manager'])) {
                $worker_dic = [];
                $worker_dic['name'] = $all['big_branch_manager'];
                $worker_dic['role_id'] = 30;
                $worker_list[] = $worker_dic;
            }
            if (isset($all['vice_President'])) {
                $worker_dic = [];
                $worker_dic['name'] = $all['vice_President'];
                $worker_dic['role_id'] = 29;
                $worker_list[] = $worker_dic;
            }
            if (isset($all['President'])) {
                $worker_dic = [];
                $worker_dic['name'] = $all['President'];
                $worker_dic['role_id'] = 39;
                $worker_list[] = $worker_dic;
            }
            $worker_first_dic = $worker_list[0];
            $workers = Worker::where('name', $worker_first_dic['name'])->first();
            $worker_id = $workers->id;      // 订单归属人的id
            // 是否有银行卡号状态
            $bank_card_id = 0;
            // 判断并查找相应用户
            $users = $all['users'];                 //投资人姓名
            $usersList = User::where('name', $users)->where('worker_id', $worker_id)->first();
            if (empty($usersList)) {
                if ($users == '到期') {
                    $user = new User();
                    $user->name = $users;
                    $user->password = CommonFun::md5('666666');
                    $user->is_first = '1';
                    $user->user_status = '1';
                    $user->remark = '该用户为无信息用户群统一数据';
                    $user->save();
                    $user_id = $user->id;
                } else {
                    $user = new User();
                    if (isset($all['user_id_card'])) {
                        $userIdCard = $all['user_id_card'];       //投资人身份证号
                        $birth = substr($userIdCard, 6, 8);
                        $year = substr($birth, 0, 4);
                        $mouth = substr($birth, 4, 2);
                        $day = substr($birth, 6, 2);
                        $birthday = $year . '-' . $mouth . '-' . $day;      //通过身份证号解析出的生日
                        $sex = substr($userIdCard, 16, 1);          //通过身份证解析出性别
                        $user->sex = $sex;
                        $user->birthday = $birthday;
                        $user->password = CommonFun::md5(substr($userIdCard, 11, 6));
                        $user->id_number = $userIdCard;
                    } else {
                        $user->password = CommonFun::md5('666666');
                    }
                    //创建
                    $user->name = $users;
                    $user->id_type = '1';
                    $user->is_first = '1';
                    $user->user_status = '1';
                    $user->worker_id = $worker_id;
                    $user->branch_id = $branch_id;
                    $user->remark = '该用户由系统自动生成，请完善其他信息';
                    $user->save();
                    $user_id = $user->id;
                }
                // 验证银行卡-不存在的银行创建，存在的提取信息，用户绑定
                if ($all['bank_name']) {
                    $bankNames = $all['bank_name'];
                    $bankName = (explode("银行", $bankNames)[0]) . "银行";
                    $banks = Bank::where('bank_name', $bankName)->first();
                    if (empty($banks)) {
                        //创建银行信息
                        $bank = new Bank();
                        $bank->bank_name = $bankName;
                        $bank->type = '1';
                        $bank->weight = 100;
                        $bank->save();
                        $bank_id = $bank->id;
                    } else {
                        $bank_id = $banks->id;
                    }
                    if ($all['bank_card_id']) {
                        // 银行卡绑定
                        $bank_card_list = BankCard::where('user_id', $user_id)->where('bank_id', $bank_id)->where('bank_num', $all['bank_card_id'])->first();
                        if (empty($bank_card_list)) {
                            $bankCards = new BankCard();
                            $bankCards->bank_id = $bank_id;
                            $bankCards->bank_num = $all['bank_card_id'];
                            $bankCards->user_id = $user_id;
                            $bankCards->bank_sum_name = $bankNames;
                            $bankCards->account_name = $users;
                            $bankCards->is_default = 2;
                            $bankCards->save();
                            $bank_card_id = $bankCards->id;
                        } else {
                            $bank_card_id = $bank_card_list->id;
                        }
                    }
                }
            } else {
                // 有结果补充信息
                $user_id = $usersList->id;
                if (isset($all['user_id_card']) && $all['user_id_card'] != '') {
                    $usersList->id_number = $all['user_id_card'];
                    $usersList->save();
                }
                // 修改银行卡
                if (isset($all['bank_name'])) {
                    $bankNames = $all['bank_name'];
                    $bankName = (explode("银行", $bankNames)[0]) . "银行";
                    $banks = Bank::where('bank_name', $bankName)->first();
                    if (empty($banks)) {
                        //创建银行信息
                        $bank = new Bank();
                        $bank->bank_name = $bankName;
                        $bank->type = '1';
                        $bank->weight = 100;
                        $bank->save();
                        $bank_id = $bank->id;
                    } else {
                        $bank_id = $banks->id;
                    }
                }
                if (isset($all['bank_card_id'])) {
                    // 银行卡绑定
                    $bank_card_list = BankCard::where('user_id', $user_id)->where('bank_id', $bank_id)->where('bank_num', $all['bank_card_id'])->first();
                    if (empty($bank_card_list)) {
                        $bankCards = new BankCard();
                        $bankCards->bank_id = $bank_id;
                        $bankCards->bank_num = $all['bank_card_id'];
                        $bankCards->user_id = $user_id;
                        $bankCards->bank_sum_name = $bankNames;
                        $bankCards->account_name = $users;
                        $bankCards->is_default = 1;
                        $bankCards->save();
                        $bank_card_id = $bankCards->id;
                    } else {
                        $bank_card_id = $bank_card_list->id;
                    }
                }
            }
            $products = Product::where('id', $product_id)->first();
            if (isset($all['contract_no'])) {
                $is_repeat = Order::where('contract_no', $all['contract_no'])->first();
                if ($is_repeat) {
                    return LMReturn::insertErr('订单重复', '401');
                }
            }
            $mounths = $all['long_time'];
            $rate = $all['rate'];
            $payType = $this->switchCase($all['pay_type']);
            $nums = $this->get_num($mounths, $payType);
            $end_date_time = $this->get_date($all['sign_time'], $mounths, 0);
            $orders = new Order();
            $orders->order_no = time() . str_pad(strrev($user_id), 4, '0', STR_PAD_RIGHT);                        // 订单编号(封装一个方法生成订单编号)
            $orders->contract_no = $all['contract_no'];                             // 合同编号
            $orders->user_id = $user_id;                                            // 用户id
            $orders->worker_id = $worker_id;                                            // 客户经理id
            if ($branch_id != '') {
                $orders->branch_id = $branch_id;                                          // 部门id
            }
            $orders->product_id = $product_id;                                      // 产品id
            if ($bank_card_id != 0) {
                $orders->bank_card_id = $bank_card_id;                           // 银行卡号
            }
            $orders->order_status = $all['end_date'] > date("Y-m-d", time()) ? 4 : 6;      // 订单状态（4是已生效  6是已结束  ）
            $is_first = $this->switchCase2($all['is_first']);
            if ($is_first != 4) {
                $orders->is_first = $is_first;          // 1.新单、2.存量、3.转续
            }
            foreach ($worker_list as $worker_dic) {
                $other_worker = Worker::where('name', $worker_dic['name'])->first();
                $other_worker_id = $other_worker->id;
                if ($worker_dic['role_id'] == 44) {
                    $orders->wealth_manage_id = $other_worker_id;
                } elseif ($worker_dic['role_id'] == 33) {
                    $orders->group_manage_id = $other_worker_id;
                } elseif ($worker_dic['role_id'] == 32) {
                    $orders->sales_manage_id = $other_worker_id;
                } elseif ($worker_dic['role_id'] == 31) {
                    $orders->department_manage_id = $other_worker_id;
                } elseif ($worker_dic['role_id'] == 30) {
                    $orders->regional_manage_id = $other_worker_id;
                } elseif ($worker_dic['role_id'] == 29) {
                    $orders->vice_president_id = $other_worker_id;
                } elseif ($worker_dic['role_id'] == 39) {
                    $orders->president_id = $other_worker_id;
                }
            }
            $orders->audit_step = 3;                                                // 审核步数 1初审， 2复审   3正常订单
            $orders->is_first_instance = 1;                                         // 审核状态 1通过  2拒绝 3待审核
            $orders->record_worker_id = $operation_id;                              // 录入人
            if ($all['investment_way']) {
                if ($all['investment_way'] == '银行转账') {
                    $orders->investment_way = 1;
                } elseif ($all['investment_way'] == 'POS刷卡') {
                    $orders->investment_way = 2;
                } elseif ($all['investment_way'] == '转存') {
                    $orders->investment_way = 3;
                } elseif ($all['investment_way'] == 'POS刷卡/（银行转账）') {
                    $orders->investment_way = 4;
                }
            }                                       // 投资方式
            $orders->results_rate = $products->results_rate;                        // 年化比例
            $orders->order_money = $all['order_money'];                             // 订单金额
            $orders->results_money = $all['results_money'];                         // 年化业绩
            $orders->contract_end_date = $end_date_time;                            // 合同截止日期
            $orders->sign_time = $all['sign_time'];                                 // 签约日期
            $orders->valid_time = $all['sign_time'];                                // 生效日期
            if (isset($all['family_bank'])) {
                if ($all['family_bank'] == '民生银行') {
                    $orders->families_id = 1;                              // 工户id
                } elseif ($all['family_bank'] == '建设银行') {
                    $orders->families_id = 2;                              // 工户id
                } elseif ($all['family_bank'] == '民生银行/建设银行') {
                    $orders->families_id = 4;                              // 工户id
                }
            }
            $orders->account_time = $all['account_time'];                           // 到账日期
            $orders->course_money = $all['course_money'];                           // 手续费
            $orders->remark = $all['remark'];
            $orders->save();
            $order_id = $orders->id;
            $order_recodes = new OrderRecord();
            $order_recodes->order_id = $order_id;
            $order_recodes->type = 2;
            $order_recodes->worker_id = $operation_id;
            $order_recodes->is_success = 1;
            $order_recodes->remark = '订单生成--该条记录由数据导入生成';
            $order_recodes->save();
            for ($num = 1; $num <= $nums; $num++) {
                $interest = round(($all['order_money'] * ($rate)) / 12 * $mounths / $nums, 2);
                //                $interests = ceil($interest);
                $stas = ($num + 1) == $nums ? 1 : 0;
                $next_date = $num == $nums ? '' : $this->get_date($all['sign_time'], ($mounths / $nums) * ($num + 1), $stas);
                $money = $num == $nums ? $all['order_money'] : 0;
                $stas = $num == $nums ? 1 : 0;
                $interst_recode = new InterestRecord();
                $interst_recode->order_id = $order_id;
                if ($branch_id != '') {
                    $interst_recode->branch_id = $branch_id;                                          // 部门id
                }
                $interst_recode->date = $this->get_date($all['sign_time'], ($mounths / $nums) * $num, $stas);
                $interst_recode->next_date = $next_date;
                $interst_recode->interest = $interest;
                $interst_recode->money = $money;
                $interst_recode->payment_amount = ceil($money + $interest);
                $interst_recode->type = 2;
                $interst_recode->status = 1;
                $interst_recode->num = $num;
                $interst_recode->num_all = $nums;
                $interst_recode->save();
            }
        } catch (\Throwable $th) {
            DB::rollback();             // 数据库回滚
            return LMReturn::insertErr('缺少参数', '402');
        }
        DB::commit(); //提交至数据库
        return LMReturn::Success();
    }

    // 计算生成多少条记录
    public function get_num($mounths, $payType)
    {
        switch ($payType) {
            case '1':
                $num = $mounths;
                break;
            case '3':
                $num = $mounths / 3;
                break;
            case '12':
                $num = $mounths / 12;
                break;
            default:
                $num = 1;
                break;
        }
        return $num;
    }

    // 计算第$num个月的打款日期
    public function get_date($date, $num, $stas)
    {
        $dates = explode('-', $date);
        $year = $dates[0];
        $mounth = $dates[1];
        $day = $dates[2];
        if ($mounth + $num > 12) {
            $years = $year + 1;
            $mounths = $mounth + $num - 12;
        } else {
            $years = $year;
            $mounths = $mounth + $num;
        }
        $is_two = (($years % 4) == 0) ? 29 : 28;
        switch ($mounths) {
            case 4:
            case 6:
            case 9:
            case 11:
                $days = $day == 31 ? 30 : $day;
                break;
            case 2:
                $days = $day > 28 ? $is_two : $day;
                break;
            default:
                $days = $day;
                break;
        }
        $days = $stas == 1 ? $days - 1 : $days;
        return $years . '-' . $mounths . '-' . $days;
    }

    public function importToExport(Request $request)
    {
        //去验证token值
        $tokenInfo = json_decode($this->verifyTokenLm(), true);
        //判断传递过来的token的code码是否等于400  如果是400的话，提示token不正确
        if ($tokenInfo['code'] == 400) {
            return LMReturn::tokenErr($tokenInfo['msg'], $tokenInfo['code']);
        }
        //判断文件格式
        $file = $request->file('excel');
        if (empty($file)) {
            return back()->withInput()->with('questionError', '请上传需转换文件');
        }

        if (!in_array($file->getClientOriginalExtension(), ['xls', 'xlsx'])) {

            return back()->withErrors(['import' => '文件仅支持xls、xlsx'])->withInput();
        }

        //获取文件
        $fileName = $tokenInfo['worker_id'] . time() . '.' . $file->getClientOriginalExtension();
        $filePath = public_path('excel/' . date("Ymd") . '/');
        $file->move($filePath, $fileName);
        //phpExcel初始化
        $reader = IOFactory::createReader('Xlsx'); //设置以Excel5格式(Excel97-2003工作簿)
        $phpExcel = $reader->load($filePath . $fileName); //载入excel文件
        $excelSheet = $phpExcel->getSheet(0); //读取第一个工作表

        $highestRow = $excelSheet->getHighestRow(); //取得总行数
        $highestColumn = $excelSheet->getHighestColumn(); //取得总列数
        //获取单元格数据
        $A_Z = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC'];
        $i = 0;
        while ($highestColumn != $A_Z[$i]) {
            $i++;
            $num = $i;
        }
        $recordData = []; //行号从1开始

        for ($row = 1; $row <= $highestRow; $row++) {
            //列数是以A列开始
            for ($j = 0; $j <= $num; $j++) {
                $col = $A_Z[$j];
                $cellValue = $excelSheet->getCell($col . $row)->getCalculatedValue();
                if (is_object($cellValue)) {
                    $cell = $cellValue->__toString();
                };
                switch ($col) {
                    case 'A':
                        $key = 'sign_time';
                        break;
                    case 'B':
                        $key = 'contract_no';
                        break;
                    case 'C':
                        $key = 'users';
                        break;
                    case 'D':
                        $key = 'order_money';
                        break;
                    case 'E':
                        $key = 'results_money';
                        break;
                    case 'F':
                        $key = 'long_time';
                        break;
                    case 'G':
                        $key = 'investment_way';
                        break;
                    case 'H':
                        $key = 'family_bank';
                        break;
                    case 'I':
                        $key = 'account_time';
                        break;
                    case 'J':
                        $key = 'course_money';
                        break;
                    case 'K':
                        $key = 'pay_type';
                        break;
                    case 'L':
                        $key = 'rate';
                        break;
                    case 'M':
                        $key = 'is_first';
                        break;
                    case 'N':
                        $key = 'end_date';
                        break;
                    case 'O':
                        $key = 'user_id_card';
                        break;
                    case 'P':
                        $key = 'bank_card_id';
                        break;
                    case 'Q':
                        $key = 'bank_name';
                        break;
                    case 'R':
                        $key = 'customer_mangaer';
                        break;
                    case 'S':
                        $key = 'group_manager';
                        break;
                    case 'T':
                        $key = 'department_manager';
                        break;
                    case 'U':
                        $key = 'branch_manager';
                        break;
                    case 'V':
                        $key = 'big_branch_manager';
                        break;
                    case 'W':
                        $key = 'vice_President';
                        break;
                    case 'X':
                        $key = 'President';
                        break;
                    case 'Y':
                        $key = 'branch';
                        break;
                    case 'Z':
                        $key = 'remark';
                        break;
                    default:
                        $key = $col;
                        break;
                }
                if (($key == 'sign_time' || $key == 'end_date' || $key == 'account_time') && $row != 1) {
                    $strs = strstr($cellValue, '.');
                    if ($strs) {
                        $strss = explode('.', $cellValue);
                        $year = $strss[0];
                        $mouth = $strss[1];
                        $day = $strss[2];
                        if ($mouth < 10) {
                            if ($day < 10) {
                                $res_data = $year . '-' . '0' . $mouth . '-' . '0' . $day;
                            } else {
                                $res_data = $year . '-' . '0' . $mouth . '-' . $day;
                            }
                        } else {
                            if ($day < 10) {
                                $res_data = $year . '-' . $mouth . '-' . '0' . $day;
                            } else {
                                $res_data = $year . '-' . $mouth . '-' . $day;
                            }
                        }
                    } else {
                        $toTimestamp = Date::excelToTimestamp($cellValue);
                        $res_data = date('Y-m-d', $toTimestamp) == '1970-01-01' ? '' : date('Y-m-d', $toTimestamp);
                    }
                } else {
                    $res_data = $this->handleStr($cellValue);
                }
                $rowData[$key] = $res_data;
            }
            $recordData[] = $rowData;
        }
        return $recordData;
    }

    public function switchCase($name)
    {
        switch ($name) {
            case '月付已到期':
            case '月付':
                $stas = 1;
                break;
            case '季付':
            case '季付已到期':
                $stas = 3;
                break;
            case '年付':
            case '年付已到期':
                $stas = 12;
                break;
            default:
                $stas = 2;
                break;
        }
        return $stas;
    }

    public function switchCase2($name)
    {
        switch ($name) {
            case '新单':
                return 1;
            case '转续':
                return 2;
            case '存量':
                return 3;
            default:
                return 4;
        }
    }

    /**
     * @description: 这是例子用到的处理单元格方法：
     * @param string $str
     * @return string
     */
    private function handleStr($str)
    {

        $str = trim($str); //清除字符串两边的空格

        $str = preg_replace("/\t/", "", $str); //使用正则表达式替换内容，如：空格，换行，并将替换为空。

        $str = preg_replace("/\r\n/", "", $str);

        $str = preg_replace("/\r/", "", $str);

        $str = preg_replace("/\n/", "", $str);

        $str = preg_replace("//", "", $str);

        $str = preg_replace("//", "", $str); //匹配html中的空格

        return trim($str); //返回字符串

    }
}
